#!/bin/bash

#采集日期
if [ $# -eq 0 ]
then
    DATE=`date -d "-1 day" +%Y-%m-%d`
else
    DATE=$1
fi

YEARSTR=`date -d "${DATE}" +%Y`
MONTHSTR=`date -d "${DATE}" +%m`
DAYSTR=`date -d "${DATE}" +%d`

hive -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;

CREATE TABLE IF NOT EXISTS itcast_dimen.itcast_clazz_update (
  id int COMMENT 'ems课程id(非自增)',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  itcast_school_id STRING COMMENT 'ems校区ID',
  itcast_school_name STRING COMMENT 'ems校区名称',
  itcast_subject_id STRING COMMENT 'ems学科ID',
  itcast_subject_name STRING COMMENT 'ems学科名称',
  itcast_brand STRING COMMENT 'ems品牌',
  clazz_type_state STRING COMMENT '班级类型状态',
  clazz_type_name STRING COMMENT '班级类型名称',
  teaching_mode STRING COMMENT '授课模式',
  start_time STRING COMMENT '开班时间',
  end_time STRING COMMENT '毕业时间',
  comment STRING COMMENT '备注',
  detail STRING COMMENT '详情(比如：27期)',
  uncertain STRING COMMENT '待定班(0:否,1:是)',
  tenant int COMMENT '租户',
  ends_time STRING COMMENT '有效时间')
comment '班级信息表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='id');


CREATE TABLE IF NOT EXISTS itcast_dimen.itcast_clazz_tmp (
  id int COMMENT 'ems课程id(非自增)',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  itcast_school_id STRING COMMENT 'ems校区ID',
  itcast_school_name STRING COMMENT 'ems校区名称',
  itcast_subject_id STRING COMMENT 'ems学科ID',
  itcast_subject_name STRING COMMENT 'ems学科名称',
  itcast_brand STRING COMMENT 'ems品牌',
  clazz_type_state STRING COMMENT '班级类型状态',
  clazz_type_name STRING COMMENT '班级类型名称',
  teaching_mode STRING COMMENT '授课模式',
  start_time STRING COMMENT '开班时间',
  end_time STRING COMMENT '毕业时间',
  comment STRING COMMENT '备注',
  detail STRING COMMENT '详情(比如：27期)',
  uncertain STRING COMMENT '待定班(0:否,1:是)',
  tenant int COMMENT '租户',
  ends_time STRING COMMENT '有效时间')
comment '班级信息表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='id');"

sqoop import \
--connect jdbc:mysql://192.168.10.124:3306/scrm \
--username root \
--password 123456 \
--query 'select *,FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as starts_time,date_format("9999-12-31","%Y-%m-%d") as ends_time from itcast_clazz where starts_time = ${DATESTR} and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_clazz_update \
-m 10 \
--split-by id

hive -S -e "
INSERT INTO itcast_dimen.itcast_clazz_tmp PARTITION (starts_time) 
SELECT 
  ic.id,
  ic.create_date_time,
  ic.update_date_time,
  ic.deleted,
  ic.itcast_school_id,
  ic.itcast_school_name,
  ic.itcast_subject_id,
  ic.itcast_subject_name,
  ic.itcast_brand,
  ic.clazz_type_state,
  ic.clazz_type_name,
  ic.teaching_mode,
  ic.start_time,
  ic.end_time,
  ic.comment,
  ic.detail,
  ic.uncertain,
  ic.tenant,
  IF(up.id IS NOT NULL, IF(ic.ends_time = '9999-12-31', up.update_date_time, ic.ends_time) , cc.ends_time) AS ends_time
from itcast_dimen.itcast_clazz ic 
LEFT JOIN itcast_dimen.itcast_clazz_update up on ic.id = up.id
UNION ALL
SELECT * FROM itcast_dimen.itcast_clazz_update;

INSERT OVERWRITE itcast_dimen.itcast_clazz PARTITION (starts_time) 
SELECT * FROM itcast_dimen.itcast_clazz_tmp;

DROP TABLE itcast_ods.itcast_clazz_tmp;
DROP TABLE itcast_ods.itcast_clazz_update;"